import pandas as pd
import os
import glob
from openpyxl import Workbook
from tkinter import *
from tkinter.ttk import *
from datetime import datetime
def get_date():
    return "["+str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))+"]"
def export(info):
    folder_path = 'd:/data'
    output_file = 'D:/JMP.xlsx'
    start_row = 39
    end_row = 71
    final = []
    excel_files =glob.glob(os.path.join(folder_path, '*.xlsx'))
    for excel_file in excel_files:
        with pd.ExcelFile(excel_file) as xlsx:
            sheet=xlsx.sheet_names
            for sht in sheet:
                if sht[:8]=="Raw data":
                    tool=sht[9:]
                    df=pd.read_excel(excel_file,sht)
                    for i in range(start_row, end_row):
                        local=[tool]
                        row=df.iloc[i].to_list()
                        for j in range(15,len(row)):
                            if isinstance(row[j], (float, int)) and pd.notnull(row[j]):
                                local.append(row[j])
                                info_str=get_date()+"..."
                                info.config(text=info_str)
                        final.append(local)
    wb=Workbook()
    ws=wb.active
    ws.title="data"
    ws.append(['CAV','spc'])
    for f in final:
        ws.append(f)
    wb.save(output_file)
    info_str=get_date()+" 导出成功"
    info.config(text=info_str)
app=Tk()
app.title("Cpk To JMP")
app.geometry("400x200")
app.resizable(False, False)
app.config(bg="white")
style=Style()
style.configure(".", font=("SF Hello Light", 16),background="white")
Label(app, text="Cpk文件放置D盘data文件夹").place(relx=0.5, rely=0.2, anchor="center")
info=Label(app, text="")
info.place(relx=0.5, rely=0.8, anchor="center")
btn = Button(app, text="Start", command=lambda:export(info))
btn.place(relx=0.3, rely=0.35)
app.mainloop()